﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace TestJQGrid
{
    public partial class WebForm2 : CommanUI
    {
        #region Global declarations
        
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fill_fields();
                fill_Funs();
                fill_dlFormula();

                lblWhenExp.Text = "";
                lblThenExp.Text = "";
                tableNames.Value = "";
                btnSave.Visible = false;
                ViewState["formulaIDToUpdate"] = 0;
            }

         //   btnReset.Attributes.Add("OnClick", "document.forms[0].reset();return false;");
        }

        private void fill_fields()
        {
            DataLayer ddl = new DataLayer();
            listFields.DataSource = ddl.getDataSource("select (cp.TableName + '.' + cp.ColumnName) AS ColumnsList,('@('+cp.TableName + '.' + cp.ColumnName+')') AS ColumnsListVal  from ColumnProperties cp where cp.TableName in ( select tr.RelatedTable from TableRelation tr, ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "' AND pl.TableName = tr.PrimaryTable ) OR cp.TableName in ( select pl.TableName from ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "') AND cp.ColumnName not in ('EntryStatus','Product','TransId','ProductName') Order By ColumnsList");
            listFields.DataTextField = "ColumnsList";
            listFields.DataValueField = "ColumnsListVal";
            listFields.DataBind();

            listFields.Width = 200;
        }

        private void fill_Funs()
        {
            DataLayer ddl = new DataLayer();
            listFormula.DataSource = ddl.getDataSource("SELECT Value FROM ValidValues WHERE (TableName = 'Formula')");
            listFormula.DataTextField = "Value";
            listFormula.DataValueField = "Value";
            listFormula.DataBind();

            listFormula.Width = 100;
        }

        private void fill_dlFormula()
        {
            DataLayer ddl = new DataLayer();
            dlFormula.DataSource = ddl.getDataSource("select FormulaName,ID from FormulaTable where FormulaType='Formula'");
            dlFormula.DataBind();
        }

        protected void btnValidate_Click(object sender, EventArgs e)
        {
            //if (validateForm() == true)
            //{
            string result = "";
            string when_expResult = "", when_expResultType = "";
            string then_expResult = "", then_expResultType = "";
            string when_expression = txtWhenPart.Text.Trim().Replace(" ", "");
            string then_expression = txtThenPart.Text.Trim().Replace(" ", "");

                if (when_expression.Length > 0)
                {
                    object obj = isValidExp(when_expression);
                    when_expResult = Convert.ToString(obj);
                    when_expResultType = obj.GetType().ToString();
                }

                if (then_expression.Length > 0)
                {
                    object obj = isValidExp(then_expression);
                    then_expResult = Convert.ToString(obj);
                    then_expResultType = obj.GetType().ToString();
                }

                if (when_expResult !="" &&  when_expResult.ToUpper() != "TRUE" && when_expResult.ToUpper() != "FALSE") //when_expResult.IndexOf("Invalid") > -1
                {
                    if (string.IsNullOrEmpty(result))
                    {
                        result = " WHEN expression";
                    }
                    else
                    {
                        result += " and WHEN expression";
                    }

                    lblWhenExp.Text = "Invalid expression";
                    lblWhenExp.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    if (when_expResult != "")
                    {
                        lblWhenExp.Text = "Valid expression";
                        lblWhenExp.ForeColor = System.Drawing.Color.Green;
                    }
                }

                if (then_expResult.IndexOf("Invalid") > -1)
                {
                    if (string.IsNullOrEmpty(result))
                    {
                        result = " Then expression";
                    }
                    else
                    {
                        result += " and Then expression";
                    }

                    lblThenExp.Text = "Invalid expression";
                    lblThenExp.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    lblThenExp.Text = "Valid expression";
                    lblThenExp.ForeColor = System.Drawing.Color.Green;
                }

                if (result != "")
                {
                    btnSave.Visible = false;
                }
                else
                {
                    btnSave.Visible = true;
                }
           // }            
        }

        private bool validateForm()
        {
            string msg = "";

            if (txtFurmulaName.Text.Trim().Replace(" ", "").Length == 0 || txtFurmulaName.Text == string.Empty)
            {
                msg += "Please enter the formula name.";
            }
            
            if (txtThenPart.Text.Trim().Replace(" ", "").Length == 0 || txtThenPart.Text == string.Empty)
            {
                msg += "Please enter the Then Part.";
            }

            if (msg != "")
            {
                showMessage(msg);
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// This function save's the formula in the table.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string strGroup = "";
            string strWhere = "";
            string expQuery = txtThenPart.Text.Trim().Replace(" ", "");
            
            // Group By Query
            if (expQuery.IndexOf("SUM") > -1 || expQuery.IndexOf("MIN") > -1 || expQuery.IndexOf("MAX") > -1 || expQuery.IndexOf("AVG") > -1 || expQuery.IndexOf("COUNT") > -1)
            {
                strGroup = txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strGroup = strGroup.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                
                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames.Value;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;
                    expQuery += " group by " + strGroup;
                    //expQuery += " having " + txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                
            }
            else // Without GROUP BY clause
            {
                strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                
                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames.Value;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;
                
            }

            DataLayer ddl = new DataLayer();

            if (Convert.ToInt32(ViewState["formulaIDToUpdate"]) != 0)
            {
                if (ddl.insertQuery("update FormulaTable set FormulaName='" + txtFurmulaName.Text.Trim() + "',WhenPart='" + txtWhenPart.Text.Trim().Replace(" ", "") + "',ThenPart='" + txtThenPart.Text.Trim().Replace(" ", "") + "',ReturnType='" + ddReturnType.SelectedValue.ToString() + "',FinalQuery='" + expQuery.Trim() + "' where ID=" + Convert.ToInt32(ViewState["formulaIDToUpdate"])) > 0)
                {
                    fill_dlFormula();
                    showMessage("Formula updated successfully.");
                }
            }
            else
            {
                string query = "insert into FormulaTable ( FormulaName, WhenPart, ThenPart, ReturnType, FormulaType, CreatedOn, CreatedBy, FinalQuery, ProductName )" ;
                query += " values('" + txtFurmulaName.Text.Trim().Replace("'", "") + "','" + txtWhenPart.Text.Trim() + "','" + txtThenPart.Text.Trim() + "','" + ddReturnType.SelectedValue.ToString();
                query += "','Formula','" + DateTime.Now.ToString() + "','" + Session["loginId"].ToString() + "','" + expQuery + "','" + Session["ProductName"].ToString() + "') ";

                int fid = GlobalMethods.ExecuteIdentityInsert(query, "FormulaID");

                if (fid > 0)
                {
                    fill_dlFormula();
                    showMessage("Formula Saved Successfully");
                }
            }
        }

        private string getResult(string result,string val)
        {
            if (string.IsNullOrEmpty(result))
            {
                result = val;
            }
            else
            {
                result+=","+val;
            }

            return result;
        }

        protected void lbt_formula_Click(object sender, EventArgs e)
        {
            int rid = 0;
            LinkButton lbtn = sender as LinkButton;

            if (lbtn != null)
            {
                rid = Convert.ToInt32(lbtn.CommandArgument);
                showFormulDetails(rid);
                ViewState["formulaIDToUpdate"] = rid;
            }
        }

        private void showFormulDetails(int fid)
        {
            DataLayer ddl = new DataLayer();
            DataTable dt = new DataTable();
            dt = ddl.getDataSource("select * from FormulaTable ft where ft.ID=" + fid);

            if (dt != null && dt.Rows.Count > 0)
            {
                txtFurmulaName.Text = dt.Rows[0]["FormulaName"].ToString();
                ddReturnType.SelectedValue = dt.Rows[0]["ReturnType"].ToString();
                txtWhenPart.Text = dt.Rows[0]["WhenPart"].ToString();
                txtThenPart.Text = dt.Rows[0]["ThenPArt"].ToString();
            }
        }

        protected void btnReset_Click(object sender, EventArgs e)
        {
            resetForm();
        }

        private void resetForm()
        {

            btnSave.Visible = false;
            ViewState["formulaIDToUpdate"] = 0;

            txtFurmulaName.Text = "";
            ddReturnType.SelectedValue = "number";
            txtThenPart.Text = "";
            txtWhenPart.Text = "";

            lblThenExp.Text = "";
            lblWhenExp.Text = "";
        }
    }
}